import json
import pandas as pd
from sqlalchemy import create_engine, Column, Integer, String, Float, Text, MetaData, Table
import glob

# 映射字段名
field_mapping_score = {
    "seriesId": "series_id",
    "seriesName": "series_name",
    "综合": "overall_score",
    "外观": "exterior_score",
    "内饰": "interior_score",
    "操控": "handling_score",
    "动力": "power_score",
    "空间": "space_score",
    "舒适性": "comfort_score"
}

field_mapping_config = {
    "seriesId": "series_id",
    "seriesName": "series_name",
    "电池保修": "battery_warranty",
    "电机类型": "motor_type",
    "最高车速": "max_speed_kmh",
    "百公里电耗": "energy_consumption_kwh_per_100km",
    "能源类型": "energy_type",
    "总电机扭矩": "total_motor_torque_nm",
    "电池类型": "battery_type",
    "驱动形式": "drive_type",
    "电机布局": "motor_layout",
    "快充时间": "fast_charge_time_hours",
    "总电机功率": "total_motor_power_kw",
    "电池容量": "battery_capacity_kwh",
    "车型": "vehicle_type",
    "充电时间": "charging_time_hours",
    "续航里程": "range_km",
    "百公里加速": "acceleration_0_100_kmh",
    "整备质量": "curb_weight_kg",
    "电机数量": "motor_count",
    "慢充时间": "slow_charge_time_hours"
}

# 读取所有 JSON 文件
json_files = glob.glob("./data/rate_confData/car_score_config_data_近*.json")

# 存储所有数据
all_score_data = []
all_config_data = []

for json_file in json_files:
    with open(json_file, "r", encoding="utf-8") as f:
        data = json.load(f)

    for series_id, info in data.items():
        # 评分信息
        score_info = {field_mapping_score[k]: v for k, v in info["score_info"].items() if k in field_mapping_score}
        score_info["id"] = len(all_score_data) + 1  # 生成自增 id
        all_score_data.append(score_info)

        # 配置信息
        config_info = {field_mapping_config[k]: v for k, v in info["config_info"].items() if k in field_mapping_config}
        config_info["id"] = len(all_config_data) + 1  # 生成自增 id
        all_config_data.append(config_info)

# 转换为 DataFrame
df_score = pd.DataFrame(all_score_data)
df_config = pd.DataFrame(all_config_data)

# 连接 MySQL 数据库
db_url = "mysql+pymysql://root:root@localhost:3306/car_sales"
engine = create_engine(db_url)

# 创建数据库表（带主键）
metadata = MetaData()

table_score = Table(
    "car_score", metadata,
    Column("id", Integer, primary_key=True, autoincrement=True),
    Column("series_id", Integer),
    Column("series_name", String(255)),
    Column("overall_score", Float),
    Column("exterior_score", Float),
    Column("interior_score", Float),
    Column("handling_score", Float),
    Column("power_score", Float),
    Column("space_score", Float),
    Column("comfort_score", Float)
)

table_config = Table(
    "car_config", metadata,
    Column("id", Integer, primary_key=True, autoincrement=True),
    Column("series_id", Integer),
    Column("series_name", String(255)),
    Column("battery_warranty", String(255)),
    Column("motor_type", String(255)),
    Column("max_speed_kmh", Integer),
    Column("energy_consumption_kwh_per_100km", String(255)),
    Column("energy_type", String(255)),
    Column("total_motor_torque_nm", String(255)),
    Column("battery_type", String(255)),
    Column("drive_type", String(255)),
    Column("motor_layout", String(255)),
    Column("fast_charge_time_hours", String(255)),
    Column("total_motor_power_kw", String(255)),
    Column("battery_capacity_kwh", String(255)),
    Column("vehicle_type", String(255)),
    Column("charging_time_hours", String(255)),
    Column("range_km", String(255)),
    Column("acceleration_0_100_kmh", String(255)),
    Column("curb_weight_kg", String(255)),
    Column("motor_count", String(255)),
    Column("slow_charge_time_hours", String(255))
)

# 在数据库中创建表
metadata.create_all(engine)

# 写入 MySQL
df_score.to_sql("car_score", con=engine, if_exists="replace", index=False)
df_config.to_sql("car_config", con=engine, if_exists="replace", index=False)

